<?php

namespace App\Admin\Exporter;

use App\Models\Family;
use App\Models\FamilyChild;
use App\Models\FamilyEdu;
use App\Models\FamilyType;
use App\Models\Hospital;
use App\Models\HospitalTeam;
use App\Models\HospitalUserFamily;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class HealthControlExporter implements WithHeadings, FromCollection, WithStyles
{
    protected $data;

    public function __construct($data)
    {
        $this->data = $data;
    }

    /**
     * 表头
     * @return string[]
     */
    public function headings(): array
    {
        $hospitalName = Hospital::query()->where('id',$this->data['hospital_id'])->value('name');
        return [
            ["{$hospitalName}0-36月儿童中医药健康管理儿童明细"],
            ['序号', '姓名', '生日','联系电话', '证件号','母亲姓名', '母亲证件','现住址','首次签约日期'],
        ];
    }

    /**
     * 导出
     * @return \Illuminate\Support\Collection|\Tightenco\Collect\Support\Collection
     */
    public function collection()
    {
        $data = $this->data;
        $i=1;
        $hospital_id = $data['hospital_id'];
        $whereArr[] = "eb_family.birthday >= '".date('Y-m-d',strtotime('-3 year'))."'";
        $whereArr[] = "eb_hospital_user_family.hospital_id = eb_import_family.hospital_id";
        $whereArr[] = "eb_hospital_user_family.family_id = eb_import_family.family_id";
        $whereArr[] = "eb_family.id = eb_hospital_user_family.family_id";
        $whereArr[] = "eb_import_family.type = 1";
        $whereArr[] = "eb_hospital_user_family.hospital_id = {$hospital_id}";
        $whereArr[] = "eb_hospital_user_family.`status` = 1";
        $where = implode(" and ", $whereArr);

        $sql = "SELECT *
FROM (SELECT
eb_family.`name`,
eb_family.birthday,
eb_hospital_user_family.hospital_id,
eb_family.id as family_id,
eb_hospital_user_family.hospital_team_id,
eb_hospital_user_family.user_id,
eb_hospital_user_family.starttime,
eb_hospital_user_family.`status`,
eb_hospital_user_family.`created_at`,

ROW_NUMBER() OVER(
    PARTITION BY eb_family.`name`,
    eb_family.birthday ORDER BY eb_hospital_user_family.starttime ASC
) RN
from eb_hospital_user_family,eb_family,eb_import_family
where
    {$where}
) M
WHERE M.RN=1";
        //echo($sql);exit;
        $models = DB::select($sql);

        $list = [];
        foreach($models as $model) {
            //dd($sign);
            $family = Family::query()->where('id',$model->family_id)->first();
            $user = User::query()->where('id',$model->user_id)->first();
            $familyChild = FamilyChild::query()->where('family_id',$model->family_id)->first();
            $row = [];
            $row[] = $i++;
            $row[] = isset($family)?$family->name:"";
            $row[] = isset($family)?$family->birthday:"";
            $row[] = isset($user)?$user->phone:"";
            $row[] = isset($family)?$family->idcard:"";
            $row[] = isset($familyChild)?$familyChild->mother:"";
            $row[] = isset($familyChild)?$familyChild->mother_idcard:"";
            $row[] = isset($family)?$family->address:"";
            $date = Carbon::parse($model->created_at);
            $formattedDate = $date->format('Y-m-d'); // 输出: 2023-10-05
            $row[] = $formattedDate;
            $list[] = $row;
        }
        $this->column = $i;
        return collect($list);
    }

    public function styles(Worksheet $sheet)
    {
        $sheet->mergeCells('A1:I1');



        $sheet->getStyle('A1:I1')->getAlignment()->setVertical('center')->setHorizontal('center');//垂直居中
        $sheet->getStyle('A1:I' . ($this->column + 2))->getAlignment()->setVertical('center')->setHorizontal('center');//垂直居中
        $sheet->getRowDimension('1')->setRowHeight(25);//设置指定行高
        $sheet->getDefaultRowDimension()->setRowHeight(22);//设置默认行高


        $sheet->getDefaultColumnDimension()->setWidth(10);
        $sheet->getColumnDimension('C')->setWidth(15);
        $sheet->getColumnDimension('D')->setWidth(15);
        $sheet->getColumnDimension('E')->setWidth(23);
        $sheet->getColumnDimension('G')->setWidth(23);
        $sheet->getColumnDimension('H')->setWidth(25);
        $sheet->getColumnDimension('I')->setWidth(15);
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //细边框
                ]
            ]
        ];
        $sheet->getStyle('A1:I' . ($this->column + 1))->applyFromArray($styleArray);
    }
}
